const express = require('express');
const cors = require('cors');
const mysql = require('mysql2/promise');
const path = require('path');
const multer = require('multer');
const fs = require('fs');
require('dotenv').config({ path: path.join(__dirname, '.env') });

const app = express();
const PORT = process.env.PORT || 3000;

// 确保上传目录存在
const uploadDir = path.join(__dirname, 'public', 'images');
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir, { recursive: true });
}

// 配置 multer 文件上传
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, uploadDir)
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9)
    const ext = path.extname(file.originalname)
    cb(null, file.fieldname + '-' + uniqueSuffix + ext)
  }
})

const upload = multer({ 
  storage: storage,
  limits: { fileSize: 5 * 1024 * 1024 }, // 5MB
  fileFilter: function (req, file, cb) {
    const allowedTypes = /jpeg|jpg|png|gif|webp/;
    const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
    const mimetype = allowedTypes.test(file.mimetype);
    if (extname && mimetype) {
      return cb(null, true);
    } else {
      cb(new Error('只支持图片格式 (jpeg, jpg, png, gif, webp)'));
    }
  }
})

// 中间件
app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// 提供静态文件服务（图片资源）
app.use('/images', express.static(path.join(__dirname, 'public', 'images')));

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || '',
  port: 3306
});


async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log("✅ 数据库连接成功");
    connection.release();
  } catch (err) {
    console.error("❌ 数据库连接失败:", err.message);
  }
}

testConnection();

module.exports = pool;
// 测试数据库连接
pool.getConnection()
  .then(connection => {
    console.log('数据库连接成功');
    connection.release();
  })
  .catch(err => {
    console.error('数据库连接失败:', err.message);
    console.error('请检查：');
    console.error('  1. MySQL服务是否运行');
    console.error('  2. 用户名和密码是否正确');
    console.error('  3. 数据库是否存在');
  });

// ==================== 商品管理 CRUD ====================
// 获取所有商品
app.get('/api/products', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products ORDER BY id DESC');
    res.json({ code: 200, data: rows, message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 获取单个商品
app.get('/api/products/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ code: 404, message: '商品不存在' });
    }
    res.json({ code: 200, data: rows[0], message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 创建商品
app.post('/api/products', async (req, res) => {
  try {
    const { name, price, description, image, category_id, stock } = req.body;
    const [result] = await pool.query(
      'INSERT INTO products (name, price, description, image, category_id, stock) VALUES (?, ?, ?, ?, ?, ?)',
      [name, price, description, image, category_id, stock]
    );
    res.json({ code: 200, data: { id: result.insertId }, message: '创建成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '创建失败', error: error.message });
  }
});

// 更新商品
app.put('/api/products/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, price, description, image, category_id, stock } = req.body;
    const [result] = await pool.query(
      'UPDATE products SET name = ?, price = ?, description = ?, image = ?, category_id = ?, stock = ? WHERE id = ?',
      [name, price, description, image, category_id, stock, id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '商品不存在' });
    }
    res.json({ code: 200, message: '更新成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '更新失败', error: error.message });
  }
});

// 删除商品
app.delete('/api/products/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [result] = await pool.query('DELETE FROM products WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '商品不存在' });
    }
    res.json({ code: 200, message: '删除成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '删除失败', error: error.message });
  }
});

// ==================== 用户管理 CRUD ====================
// 获取所有用户
app.get('/api/users', async (req, res) => {
  try {
    // 注意：这里包含password字段用于登录验证，实际生产环境应该单独做登录接口
    const [rows] = await pool.query('SELECT id, username, email, phone, password, created_at FROM users ORDER BY id DESC');
    res.json({ code: 200, data: rows, message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 获取单个用户
app.get('/api/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await pool.query('SELECT id, username, email, phone, created_at FROM users WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ code: 404, message: '用户不存在' });
    }
    res.json({ code: 200, data: rows[0], message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 创建用户
app.post('/api/users', async (req, res) => {
  try {
    const { username, email, phone, password } = req.body;
    const [result] = await pool.query(
      'INSERT INTO users (username, email, phone, password) VALUES (?, ?, ?, ?)',
      [username, email, phone, password]
    );
    res.json({ code: 200, data: { id: result.insertId }, message: '创建成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '创建失败', error: error.message });
  }
});

// 更新用户
app.put('/api/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { username, email, phone } = req.body;
    const [result] = await pool.query(
      'UPDATE users SET username = ?, email = ?, phone = ? WHERE id = ?',
      [username, email, phone, id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '用户不存在' });
    }
    res.json({ code: 200, message: '更新成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '更新失败', error: error.message });
  }
});

// 删除用户
app.delete('/api/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [result] = await pool.query('DELETE FROM users WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '用户不存在' });
    }
    res.json({ code: 200, message: '删除成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '删除失败', error: error.message });
  }
});

// ==================== 购物车管理 CRUD ====================
// 获取所有购物车项（管理员）
app.get('/api/cart/all', async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT c.*, u.username, p.name as product_name, p.price, p.image,
             (p.price * c.quantity) as total_price
      FROM cart c 
      LEFT JOIN users u ON c.user_id = u.id 
      LEFT JOIN products p ON c.product_id = p.id 
      ORDER BY c.id DESC
    `);
    res.json({ code: 200, data: rows, message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 获取用户购物车
app.get('/api/cart/:userId', async (req, res) => {
  try {
    const { userId } = req.params;
    const [rows] = await pool.query(`
      SELECT c.*, p.name, p.price, p.image, p.stock,
             (p.price * c.quantity) as total_price
      FROM cart c 
      LEFT JOIN products p ON c.product_id = p.id 
      WHERE c.user_id = ?
      ORDER BY c.created_at DESC
    `, [userId]);
    res.json({ code: 200, data: rows, message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 添加到购物车
app.post('/api/cart', async (req, res) => {
  try {
    const { user_id, product_id, quantity } = req.body;
    
    // 检查是否已存在
    const [existing] = await pool.query(
      'SELECT * FROM cart WHERE user_id = ? AND product_id = ?',
      [user_id, product_id]
    );
    
    if (existing.length > 0) {
      // 已存在，更新数量
      const newQuantity = existing[0].quantity + (quantity || 1);
      await pool.query(
        'UPDATE cart SET quantity = ? WHERE user_id = ? AND product_id = ?',
        [newQuantity, user_id, product_id]
      );
      res.json({ code: 200, message: '购物车已更新' });
    } else {
      // 不存在，插入新记录
      const [result] = await pool.query(
        'INSERT INTO cart (user_id, product_id, quantity) VALUES (?, ?, ?)',
        [user_id, product_id, quantity || 1]
      );
      res.json({ code: 200, data: { id: result.insertId }, message: '添加成功' });
    }
  } catch (error) {
    res.status(500).json({ code: 500, message: '添加失败', error: error.message });
  }
});

// 更新购物车项数量
app.put('/api/cart/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { quantity } = req.body;
    const [result] = await pool.query(
      'UPDATE cart SET quantity = ? WHERE id = ?',
      [quantity, id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '购物车项不存在' });
    }
    res.json({ code: 200, message: '更新成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '更新失败', error: error.message });
  }
});

// 删除购物车项
app.delete('/api/cart/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [result] = await pool.query('DELETE FROM cart WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '购物车项不存在' });
    }
    res.json({ code: 200, message: '删除成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '删除失败', error: error.message });
  }
});

// 清空用户购物车
app.delete('/api/cart/clear/:userId', async (req, res) => {
  try {
    const { userId } = req.params;
    await pool.query('DELETE FROM cart WHERE user_id = ?', [userId]);
    res.json({ code: 200, message: '购物车已清空' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '清空失败', error: error.message });
  }
});

// ==================== 分类管理 CRUD ====================
// 获取所有分类
app.get('/api/categories', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM categories ORDER BY id DESC');
    res.json({ code: 200, data: rows, message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 获取单个分类
app.get('/api/categories/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await pool.query('SELECT * FROM categories WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ code: 404, message: '分类不存在' });
    }
    res.json({ code: 200, data: rows[0], message: '获取成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '获取失败', error: error.message });
  }
});

// 创建分类
app.post('/api/categories', async (req, res) => {
  try {
    const { name, description } = req.body;
    const [result] = await pool.query(
      'INSERT INTO categories (name, description) VALUES (?, ?)',
      [name, description]
    );
    res.json({ code: 200, data: { id: result.insertId }, message: '创建成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '创建失败', error: error.message });
  }
});

// 图片上传接口
app.post('/api/upload', upload.single('image'), (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ code: 400, message: '请选择图片文件' });
    }
    const imageUrl = `/images/${req.file.filename}`;
    res.json({ code: 200, data: { url: imageUrl }, message: '上传成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '上传失败', error: error.message });
  }
});

// 更新分类
app.put('/api/categories/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, description } = req.body;
    const [result] = await pool.query(
      'UPDATE categories SET name = ?, description = ? WHERE id = ?',
      [name, description, id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '分类不存在' });
    }
    res.json({ code: 200, message: '更新成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '更新失败', error: error.message });
  }
});

// 删除分类
app.delete('/api/categories/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const [result] = await pool.query('DELETE FROM categories WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ code: 404, message: '分类不存在' });
    }
    res.json({ code: 200, message: '删除成功' });
  } catch (error) {
    res.status(500).json({ code: 500, message: '删除失败', error: error.message });
  }
});

// 启动服务器
app.listen(PORT, () => {
  console.log(`服务器运行在 http://localhost:${PORT}`);
});

